﻿using System.Text;
using SuperiorSqlTools;
using System.Data;
using System.Data.SqlClient;
using SuperiorCommon;

namespace SuperiorShopDataAccess
{
    public class HomeDataAccess:BaseDataAccess
    {
        public static DataSet GetHomeData(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sb = new StringBuilder();
            sb.Append("select COUNT(*)as TotalProductCount from C_Product where IsDel=0 and ShopAdminId=@shopAdminId;");
            sb.Append("select COUNT(*)as TotalUserCount from C_Users where  ShopAdminId=@shopAdminId;");
            sb.Append("select COUNT(*)as TotalOrderCount from C_Order where OrderStatus=1 and ShopAdminId=@shopAdminId;");
            sb.Append("select SUM(RealityAmount) as TotalOrderAmount from C_Order where OrderStatus=1 and ShopAdminId=@shopAdminId;");
            sb.Append("select COUNT(*) as WaitSendCount from C_Order where ShopAdminId=@shopAdminId and OrderStatus=3;");
            sb.Append("select COUNT(*) as WaitServiceCount from C_Order where ShopAdminId=@shopAdminId and OrderStatus=4 and ServiceStatus=0;");
            return sqlManager.ExecuteDataset(CommandType.Text, sb.ToString(), parms);
        }

        public static DataSet GetAdminHomeData()
        {
            var sqlManager = new SqlManager();
            var sb = new StringBuilder();
            sb.Append("select COUNT(*)as TotalProductCount from C_Product where IsDel=0;");
            sb.Append("select COUNT(*)as TotalUserCount from C_Users where  ShopAdminId>0;");
            sb.Append("select COUNT(*)as TotalOrderCount from C_Order where OrderStatus=1 and ShopAdminId>0;");
            sb.Append("select SUM(RealityAmount) as TotalOrderAmount from C_Order where OrderStatus=1 and ShopAdminId>0;");
            sb.Append("select COUNT(Id)as TotalShopAdminCount from C_ShopAdmin;");
            sb.Append("select count(*) as TotalPayShopAdminCount from (select ShopAdminId from C_RechargeOrder where OrderStatus=1 group by ShopAdminId )t;");
            sb.Append("select COUNT(*) as UsingShopAdminCount from C_ShopAdmin where ProgrameExpireTime>GETDATE() or H5ExpireTime>GETDATE();");
            sb.Append("select COUNT(*)as UsingProgramCount from C_ShopApp sa left join C_ShopAdmin s on sa.ShopAdminId=s.Id where AppType=1 and sa.OptionStatus=1 and s.ProgrameExpireTime>GETDATE();");
            sb.Append("select COUNT(*)as UsingH5Count from C_ShopApp sa left join C_ShopAdmin s on sa.ShopAdminId=s.Id where AppType=2 and sa.OptionStatus=1 and s.H5ExpireTime>GETDATE();");
            sb.Append("select SUM(Amount)as TotalH5PayAmount  from C_RechargeOrder where OrderStatus=1 and AppType=2;");
            sb.Append("select SUM(Amount)as TotalProgramPayAmount  from C_RechargeOrder where OrderStatus=1 and AppType=1;");
            sb.Append("select SUM(Amount)as TotalPayAmount  from C_RechargeOrder where OrderStatus=1;");

            return sqlManager.ExecuteDataset(CommandType.Text, sb.ToString(), null);
        }


        public static DataSet GetAddUserChart(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            var dateArea = GetDataArea(1,7);
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                new SqlParameter("@mindate",dateArea.MinDate),
                new SqlParameter("@maxdate",dateArea.MaxDate)
            };
            var sql = "select CreateTime from C_Users where ShopAdminId=@shopAdminId and CreateTime >=@mindate and CreateTime<=@maxdate";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
        public static DataSet GetTypeUserChart(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select COUNT(*) as XcxCount from C_Users where ShopAdminId=@shopAdminId and AppType=1;select COUNT(*) as H5Count from C_Users where ShopAdminId = @shopAdminId and AppType = 2";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
        public static DataSet GetSallChart(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            var dateArea = GetDataArea(1, 7);
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                 new SqlParameter("@mindate",dateArea.MinDate),
                new SqlParameter("@maxdate",dateArea.MaxDate)
            };
            //sql含义：取出当前店铺，近7日按照下单日期分组的总购买量，总金额。
            var sql = " select SUM(t.RealityAmount) as TotalAmount,SUM(t.BuyNum) as TotalCount,CreateTime from(select RealityAmount,CreateTime,(select SUM(BuyNum) from C_OrderDetails where OrderId=o.Id)as BuyNum from C_Order  o with(nolock) where ShopAdminId=@shopAdminId and CreateTime >=@mindate and CreateTime<=@maxdate)t group by CreateTime";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
        public static DataSet CreateRechargeOrder(int shopAdminId, int rechargeId,out int ret)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                new SqlParameter("@rechargeId",rechargeId),
                new SqlParameter("@orderno",ToolManager.CreateNo()),
                 rtn_err
            };
            var ds = sqlManager.ExecuteDataset(CommandType.StoredProcedure, "[CreateRechareOrder]", parms);
            ret = 1;
            if (rtn_err.Value != null)
            {
               ret = int.Parse(rtn_err.Value.ToString());
            }
            
            return ds;

        }

        public static int WxRechargeCall(string orderno, string payCode, int state)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@orderno",orderno),
                new SqlParameter("@payCode",payCode),
                new SqlParameter("@state",state),
                 rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[WxRechareCall]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static DataSet GetRechargeOrderStatus(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@id",id)
            };
            var sql = "select OrderStatus from C_RechargeOrder where Id=@id";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static DataSet GetAdminAddUserChart()
        {
            var sqlManager = new SqlManager();
            var dateArea = GetDataArea(1, 7);
            SqlParameter[] parms =
            {
                new SqlParameter("@mindate",dateArea.MinDate),
                new SqlParameter("@maxdate",dateArea.MaxDate)
            };
            var sql = "select CreateTime from C_Users where CreateTime >=@mindate and CreateTime<=@maxdate";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
        public static DataSet GetAdminTypeUserChart()
        {
            var sqlManager = new SqlManager();
            var sql = "select COUNT(*) as XcxCount from C_Users where AppType=1;select COUNT(*) as H5Count from C_Users where  AppType = 2";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, null);
        }
        public static DataSet GetAdminSallChart()
        {
            var sqlManager = new SqlManager();
            var dateArea = GetDataArea(1, 7);
            SqlParameter[] parms =
            {
                 new SqlParameter("@mindate",dateArea.MinDate),
                new SqlParameter("@maxdate",dateArea.MaxDate)
            };
            //sql含义：取出当前店铺，近7日按照下单日期分组的总购买量，总金额。
            var sql = " select SUM(t.RealityAmount) as TotalAmount,SUM(t.BuyNum) as TotalCount,CreateTime from(select RealityAmount,CreateTime,(select SUM(BuyNum) from C_OrderDetails where OrderId=o.Id)as BuyNum from C_Order  o with(nolock) where  CreateTime >=@mindate and CreateTime<=@maxdate)t group by CreateTime";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
    }
}
